﻿Imports System.Data.SqlClient
Imports System.Data

Public Class ConnectDB
    Dim connect As New SqlConnection()
    Dim ConString As String = ConfigurationManager.ConnectionStrings("ConnectionStringOnline").ConnectionString
    Public Sub New()
        connect.ConnectionString = ConString

        If connect.State = ConnectionState.Closed Then
            connect.Open()
        End If
    End Sub
    Public Function loadData(ByVal sql As String) As DataTable
        Dim command As New SqlCommand(sql, connect)
        command.CommandType = CommandType.StoredProcedure
        Dim adapter As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        adapter.Fill(dt)
        Return dt
    End Function
    Public Function loadData(ByVal sql As String, ByVal name As String(), ByVal value As Object(), ByVal Nparameter As Integer) As DataTable
        Dim command As New SqlCommand(sql, connect)
        command.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To Nparameter - 1
            command.Parameters.AddWithValue(name(i), value(i))
        Next
        Dim adapter As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        adapter.Fill(dt)
        Return dt
    End Function
    Public Function updateData(ByVal sql As String, ByVal name As String(), ByVal value As Object(), ByVal Nparameter As Integer) As Integer
        Dim command As New SqlCommand(sql, connect)
        command.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To Nparameter - 1
            command.Parameters.AddWithValue(name(i), value(i))
        Next
        Return command.ExecuteNonQuery()
    End Function
    Public Function insertBulkData(ByVal sql As String, ByVal dt As DataTable, ByVal tabletype As String) As Integer
        Dim command As New SqlCommand(sql, connect)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue(tabletype, dt)
        Return command.ExecuteNonQuery()
    End Function
End Class
